/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package CapadeDatos;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import javax.swing.JOptionPane;
import modelo.beanalumno;
import modelo.beanlista;

/**
 *
 * @author jxtx
 */
public class daolistadoalumnos2 {

     public static  ArrayList listado( String cod_carr,int cod_turno ){
         ArrayList lista = new  ArrayList();
    try{
            Connection cn = ConexionBD.getInstance().getConnection();
            Statement stm = cn.createStatement();
            String sql ="CALL `PROC_matriculas`('"+cod_carr+"', '"+cod_turno+"');"; // PROCEDIMIENTO ALMACENADO
            ResultSet rs =stm.executeQuery(sql);
           while (rs.next()){
                    beanlista y  = new  beanlista();
                   y.setNombre(rs.getString(1));
                   y.setApepat(rs.getString(2));
                   y.setApemat(rs.getString(3));
                   y.setDni(rs.getString(4));
                   y.setCarrera(rs.getString(5));
                   y.setTurno(rs.getString(6));
                   y.setCodalumno(rs.getString(7));

                   lista.add(y);
        }
     }catch(Exception e){System.out.print(e);
   }
            Iterator itr = lista.iterator();
            beanlista list=new beanlista();
      /*itr.next();
      itr.next();
      list =  (beanlista)itr.next();
                JOptionPane.showMessageDialog(null,"--->"+list.getNombre());
        */
         return lista;


   }
  public static  ArrayList listadoalumnosxsem( String cod_sem ){
         ArrayList lista = new  ArrayList();
    try{
            Connection cn = ConexionBD.getInstance().getConnection();
            Statement stm = cn.createStatement();
            String sql ="CALL `proc_alumnosem`('', '"+cod_sem+"')"; // PROCEDIMIENTO ALMACENADO
            ResultSet rs =stm.executeQuery(sql);
           while (rs.next()){
                  beanlista y  = new  beanlista();
                   y.setCodalumno(rs.getString(1));
                   y.setApepat(rs.getString(2));
                   y.setApemat(rs.getString(3));
                   y.setNombre(rs.getString(4));
                    y.setDni(rs.getString(5));
                  y.setCarrera(rs.getString(6));
                  y.setTurno(rs.getString(7));
                  y.setFechadetmatri(rs.getString(8));
                   y.setUnidadesrep(rs.getString(9));
                   lista.add(y);
        }

            if(rs.wasNull()){
                JOptionPane.showMessageDialog(null,"NO INGRESADO");

            }
            else {


        }

     }catch(Exception e){System.out.print(e);
   }

      /*itr.next();
      itr.next();
      list =  (beanlista)itr.next();
                JOptionPane.showMessageDialog(null,"--->"+list.getNombre());
        */
         return lista;


   }
public void insertarmatricula(String cod,String obs){
        Boolean verdad = false;
        int k=0;
    try{
            Connection cn = ConexionBD.getInstance().getConnection();
            Connection cn2 = ConexionBD.getInstance().getConnection();

            Statement stm = cn.createStatement();
            Statement stm2 = cn2.createStatement();

            String sql ="select convert( max(cod_matricula),unsigned)  from matricula;";
            ResultSet rs =stm.executeQuery(sql);
            if(rs.wasNull()){
               verdad = false;
               JOptionPane.showMessageDialog(null,"NO INGRESADO");
            }
            else {
                verdad = true;
                JOptionPane.showMessageDialog(null,"INGRESADO+desc+ ");

rs.next();
k=1+Integer.parseInt(rs.getString(1));
            String sql2 ="insert into matricula values "
                    + "('"+cod+"', concat('0',"+k+"),'"+obs+"','ACTIVO');";

                int rs2 =stm2.executeUpdate(sql2);
            if(rs2!=-1){
               verdad = false;
               JOptionPane.showMessageDialog(null,"NO INGRESADO"+k);
            }
            else {
                verdad = true;
                JOptionPane.showMessageDialog(null,"INGRESADO+desc+ "+k);
                }
             }
    }catch(Exception e){System.out.print(e);
   }
   }
public static beanalumno matriculado(String codmat){

    beanalumno p  = new  beanalumno();

     try{
            Connection cn = ConexionBD.getInstance().getConnection();
            Statement stm = cn.createStatement();
            String sql ="select a.apellido_paterno,"+
" a.apellido_materno,a.nombres,"+
" c.nombre_carrera,a.dni,m.ESTADO"+
" from matricula as m inner join alumno as a "+
" on a.cod_alumno=m.cod_alumno "+
" inner join carrera as c "+
" on c.cod_carrera=a.cod_carrera"+
" where m.cod_matricula='"+codmat+"';"; // PROCEDIMIENTO ALMACENADO
             ResultSet rs =stm.executeQuery(sql);

                    while (rs.next()){
                   p.setApepat(rs.getString(1));
                   p.setApemat(rs.getString(2));
                   p.setNombre(rs.getString(3));
                   p.setCarrera(rs.getString(4));
                   p.setDni(rs.getString(5));
                   p.setEstado(rs.getString(6));
                   }
   JOptionPane.showMessageDialog(null,"Alumno Encontrado");

            if(rs.wasNull()){
                JOptionPane.showMessageDialog(null,"NO INGRESADO");

            }
            else {


        }

    }catch(Exception e){System.out.print(e);
   }
        return p;
   }
public void  cambiarestadomatricula(String codmat,String estado){

       try{
            Connection cn = ConexionBD.getInstance().getConnection();
            Statement stm = cn.createStatement();
            String sql;
            if (estado.equals("ACTIVO"))
            {
                sql="Update matricula set matricula.ESTADO='INACTIVO' "+
                        "where matricula.cod_matricula='"+codmat+"';"; // PROCEDIMIENTO ALMACENADO
            } else
            {
                sql = "Update matricula set matricula.ESTADO='ACTIVO' "
                        + "where matricula.cod_matricula='"+codmat+"';"; // PROCEDIMIENTO ALMACENADO
           }
            int k=stm.executeUpdate(sql);



           if(k!=0){
//                JOptionPane.showMessageDialog(null,"NO INGRESADO"+k);

            }
            else {

                JOptionPane.showMessageDialog(null,"Registro Modificado");

        }

    }catch(Exception e){System.out.print(e);
   }

   }
}
